﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

namespace ktds_test_module
{
    class C_DBConnect
    {
        public void Connect_DB()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            
            //DB 연결정보
            try
            {
                String conStr = @"server=localhost;Database=DB_IPBS_TEST;uid=sa;pwd=tpgns628";

                conn.ConnectionString = conStr;

                //실행 쿼리문
                String query = "SELECT * FROM TB_IPINFO";
                
                //쿼리 세팅
                cmd.CommandText = query;
                cmd.Connection = conn;

                //DB 오픈
                conn.Open();

                //쿼리 실행결과 담기 - SqlDataReader 또는 SqlDataAdapter를 사용하면 된다.
                SqlDataReader reader = cmd.ExecuteReader();
                String hostname = "";
                String ipinfo = "";
                String device = "";
                String manager = "";
                String nwarea = "";

                //결과값 출력하기
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        hostname = reader[0].ToString() + Environment.NewLine;
                        hostname += reader[1].ToString() + Environment.NewLine;
                        hostname += reader[2].ToString() + Environment.NewLine;
                        hostname += reader[3].ToString() + Environment.NewLine;
                        hostname += reader[4].ToString() + Environment.NewLine;

                        //hostname = reader[0].ToString();
                        //ipinfo = reader[1].ToString();
                        //device = reader[2].ToString();
                        //manager = reader[3].ToString();
                        //nwarea = reader[4].ToString();
                    }
                }

                MessageBox.Show(hostname);

                conn.Close();
                
            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.ToString());
            }

            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        public String SelectGroupKey(String ipstr)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            String groupcode = "";

            //DB 연결정보
            try
            {
                String conStr = @"server=localhost;Database=DB_IPBS_TEST;uid=sa;pwd=tpgns628";

                conn.ConnectionString = conStr;

                //실행 쿼리문
                String query = "SELECT GROUPCODE FROM TB_IPINFO WHERE IPINFO = '" + ipstr + "'";

                //쿼리 세팅
                cmd.CommandText = query;
                cmd.Connection = conn;

                //DB 오픈
                conn.Open();

                //쿼리 실행결과 담기 - SqlDataReader 또는 SqlDataAdapter를 사용하면 된다.
                SqlDataReader reader = cmd.ExecuteReader();

                //결과값 출력하기
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        groupcode = reader[0].ToString() + Environment.NewLine;
                    }
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }

            return groupcode;
        }
    }
}
